package com.kisyou.blogs;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

/**
 * // 1. 查询标题功能，带分页，每页 3 条   查询标题中带有 关键字(keyword)的文章列表，按照发布时间倒序
 * select count(*) from blogs where title like '%{keyword}%';
 *
 * select bid, title, published_at, author
 * from blogs
 * where title like '%{keyword}%'
 * order by published_at
 * limit 3 offset ?;
 */
public class 查询文章列表分页 {
    private static int 获取符合条件的文章总页数(Connection connection, int countPerPage, String keyword) throws SQLException {
        int count;
        // TODO: 有 SQL 注入的风险
        String sql = String.format("select count(*) from blogs where title like '%%%s%%'", keyword);//这里本质上是printf语句，所以我需要实质上这里的%不是sql语句中的通配符，需要在sql语句中包含%只能对其进行转移
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            try (ResultSet rs = ps.executeQuery()) {
                rs.next();

                count = rs.getInt(1);
            }
        }

        int pages = count / countPerPage;
        if (count % countPerPage != 0) {
            pages++;
        }

        return pages;
    }

    private static List<Blog> 根据页数获取符合条件的文章列表(Connection connection, int countPerPage, int numOfPage, String keyword) throws SQLException {
        List<Blog> blogList = new ArrayList<>();
        int limit = countPerPage;
        int offset = (numOfPage - 1) * countPerPage;

        // TODO: 有 SQL 注入的风险
        String sql = String.format("select " +
                "bid, title, published_at, author " +
                "from blogs " +
                "where title like '%%%s%%' " +
                "order by published_at desc " +
                "limit %d offset %d", keyword, limit, offset);
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    int bid = rs.getInt("bid");
                    String title = rs.getString("title");
                    String publishedAt = rs.getString("published_at");
                    String author = rs.getString("author");

                    Blog blog = new Blog(bid, title, publishedAt, author);
                    blogList.add(blog);
                }
            }
        }

        return blogList;
    }

    private static void 展示文章列表(List<Blog> blogList) {
        System.out.printf("一共 %d 条博客\n", blogList.size());
        for (Blog blog : blogList) {
            System.out.println(blog);
        }
    }

    public static void main(String[] args) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入查询关键词:");
        String keyword = scanner.nextLine();
        int countPerPage = 3;
        List<Blog> blogList = null;
        try (Connection c = DBUtil.connection()) {
            int pages = 获取符合条件的文章总页数(c, countPerPage, keyword);
            System.out.printf("一共有 %d 页，请选择第几页:", pages);
            int numOfPage = scanner.nextInt();
            blogList = 根据页数获取符合条件的文章列表(c, countPerPage, numOfPage, keyword);
        }
        展示文章列表(blogList);
    }
}
